package com.css.fxfzypg.modules.baseData.repository.impl;


import com.alibaba.fastjson.JSONObject;
import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.dto.HouseDTO;
import com.css.fxfzypg.modules.baseData.entity.HouseEntity;
import com.css.fxfzypg.modules.baseData.queryParams.HouseQueryParams;
import com.css.fxfzypg.modules.baseData.repository.HouseRepository;
import com.css.fxfzypg.modules.baseData.service.GdpService;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-12 12:09
 */
@Repository
public class HouseRepositoryImpl implements HouseRepository {
    @Autowired
    SysRoleService sysRoleService;

    @Autowired
    SUserService sUserService;

    @Autowired
    GdpService gdpService;

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public HouseEntity getHouseById(String id) {
        String sql = "";
        sql = "select * from data_house where house_price_id='" + id + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HouseEntity.class));

    }

    @Override
    public void updateHouse(HouseDTO houseDTO) {
        String sql = "";
        sql = "update data_house set  high_house_price =:highHousePrice ,steel_price =:steelPrice ,concrete_price =:concretePrice " +
                ",brickhun_price =:brickhunPrice ,brick_price =:brickPrice ,soil_price =:soilPrice ,wood_price=:woodPrice " +
                ",stone_price =:stonePrice ,other_price =:otherPrice ,update_time =:updateTime  ,update_user =:updateUser " +
                "where house_price_id=:housePriceId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(houseDTO);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public Map<String, Object> getAllHouseByParams(HouseQueryParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder(" select  * from data_house ");
        StringBuilder orderSql = new StringBuilder();
        if ("0".equals(queryParams.getOption())) {
            orderSql.append(" order by province_code,city_code,prefecture_code");
        }
        if ("1".equals(queryParams.getOption())) {
            orderSql.append(" order by COALESCE (update_time,create_time) desc");
        }
//        StringBuilder orderSql = new StringBuilder(" order by province_code,city_code,prefecture_code ");
        StringBuilder whereSql = new StringBuilder(" where is_delete = '" + YNEnum.N.toString() + "'");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;


        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode") + ",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.YPG_PRI_YW_ROLE) || roles.contains(FxfzConstants.YPG_PRI_GL_ROLE) && !roles.contains(FxfzConstants.YPG_COUN_GL_ROLE) && !roles.contains(FxfzConstants.YPG_COUN_YW_ROLE)) {
            whereSql.append(" and province_id = '" + sUser.getProvinceId() + "'");
        }


        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and province_id = '" + queryParams.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and city_id = '" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getPrefectureId())) {
            whereSql.append(" and prefecture_id = '" + queryParams.getPrefectureId() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HouseEntity.class)).size();
        List<HouseEntity> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(HouseEntity.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public void deleteHouse(List<String> list) {
        String sql = "update data_house set is_delete = '" + YNEnum.Y.toString() + "' where house_price_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", list);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public void importExcel(HouseEntity houseEntity) {
        String sql = "";
        sql = "update data_house set  high_house_price =:highHousePrice ,steel_price =:steelPrice ,concrete_price =:concretePrice " +
                ",brickhun_price =:brickhunPrice , brick_price =:brickPrice ,soil_price =:soilPrice , wood_price=:woodPrice " +
                ",stone_price =:stonePrice ,other_price =:otherPrice ,update_time =:updateTime ,update_user =:updateUser  " +
                "where house_price_id=:housePriceId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(houseEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public HouseEntity findPeoByProAndCityAndPre(String province, String city, String prefecture) {
        String sql = "select house_price_id from data_house where province = ? and city = ? and prefecture = ? and is_delete = '" + YNEnum.N.toString() + "'";
        HouseEntity houseEntity = new HouseEntity();
        try {
            houseEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HouseEntity.class), province, city, prefecture);
            return houseEntity;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void save(HouseEntity houseEntity) {
        String sql = "insert into data_house values(:housePriceId, :province, :city, :prefecture, :highHousePrice, :steelPrice, " +
                ":concretePrice, :brickhunPrice, :brickPrice, :soilPrice, :woodPrice, :stonePrice, :otherPrice, :updateTime, " +
                ":isDelete, :provinceId, :cityId, :prefectureId, :updateUser, :createUser, :createTime,:provinceCode, :cityCode, :prefectureCode)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(houseEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public HouseEntity getHouse(String province, String city, String prefecture) {
        String sql = "select * from data_house where is_delete = '" + YNEnum.N.toString() + "' and province ='" + province + "' and city ='" + city + "' and prefecture ='" + prefecture + "'";
        List<HouseEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HouseEntity.class));
        return list.size() > 0 ? list.get(0) : null;

    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_house where prefecture = '" + county + "' and city = '" + city + "' and province = '" + province + "' and is_delete ='0' and (high_house_price is not null or steel_price is not null or concrete_price is not null or brickhun_price is not null or brick_price is not null or soil_price is not null or wood_price is not null or stone_price is not null or other_price is not null )   ", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_house where city = '" + city + "' and province = '" + province + "' and is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String name) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_house where province = '" + name + "' and is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_house where province = ? and (TO_CHAR(update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and is_delete ='0'", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }

}
